from fastapi import APIRouter, HTTPException
from fastApi.database.base import get_db
from PyQt5.QtSql import QSqlQuery
import time
from datetime import datetime
from pydantic import BaseModel

router = APIRouter()

class OrderCreate(BaseModel):
    customer_name: str
    customer_email: str
    order_date: str
    product_id: int
    quantity: int
    price: float

@router.get("/orders/")
def read_orders(skip: int = 0, limit: int = 100):
    conn = get_db()
    rows = []

    # 增加重试机制
    max_retries = 3
    for attempt in range(max_retries):
        try:
            conn.operation_sql(f"SELECT * FROM orders LIMIT {limit} OFFSET {skip}")
            result = conn.query.record()
            while conn.query.next():
                col = []
                for i in range(result.count()):
                    col.append(conn.query.value(i))
                rows.append(col)
            break
        except Exception as e:
            print(f"第 {attempt + 1} 次尝试获取订单列表失败: {e}")
            if attempt < max_retries - 1:
                time.sleep(0.5)  # 等待0.5秒后重试
            else:
                raise HTTPException(status_code=500, detail="获取订单列表失败")

    orders = []
    for row in rows:
        # 规范化 order_date 为 YYYY-MM-DD
        order_date = str(row[3])
        try:
            # 尝试解析可能的日期格式
            for fmt in ["%Y-%m-%d", "%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M:%S", "%Y-%m-%dT%H:%M:%S.%fZ"]:
                try:
                    parsed_date = datetime.strptime(order_date, fmt)
                    order_date = parsed_date.strftime("%Y-%m-%d")
                    break
                except ValueError:
                    continue
            else:
                # 如果无法解析，使用当前日期并记录日志
                order_date = datetime.now().strftime("%Y-%m-%d")
                print(f"无法解析订单日期: {row[3]}，使用当前日期: {order_date}")
        except Exception as e:
            print(f"处理订单日期时出错: {row[3]}，错误: {e}")
            order_date = datetime.now().strftime("%Y-%m-%d")

        orders.append({
            "id": row[0],
            "customer_name": row[1],
            "customer_email": row[2],
            "order_date": order_date,
            "product_id": row[4],
            "quantity": row[5],
            "price": row[6]
        })

    return orders

@router.post("/orders/")
def create_order(order: OrderCreate):
    conn = get_db()
    try:
        max_retries = 3
        for attempt in range(max_retries):
            try:
                query = QSqlQuery()
                query.prepare(
                    "INSERT INTO orders (customer_name, customer_email, order_date, product_id, quantity, price) "
                    "VALUES (?, ?, ?, ?, ?, ?)"
                )
                query.addBindValue(order.customer_name)
                query.addBindValue(order.customer_email)
                query.addBindValue(order.order_date)
                query.addBindValue(order.product_id)
                query.addBindValue(order.quantity)
                query.addBindValue(order.price)

                if not query.exec_():
                    error_text = query.lastError().text()
                    print(f"SQL执行失败：{error_text}")
                    raise HTTPException(status_code=500, detail=f"创建订单失败: {error_text}")

                # 获取新插入的订单ID
                query.exec_("SELECT last_insert_rowid()")
                if query.next():
                    order_id = query.value(0)
                else:
                    raise HTTPException(status_code=500, detail="无法获取新订单ID")

                return {"message": f"订单 {order_id} 创建成功", "order_id": order_id}
            except Exception as e:
                print(f"第 {attempt + 1} 次尝试创建订单失败: {e}")
                if attempt < max_retries - 1:
                    time.sleep(0.5)
                else:
                    raise HTTPException(status_code=500, detail="创建订单失败")
    except Exception as e:
        print(f"创建订单错误: {e}")
        raise HTTPException(status_code=500, detail=f"创建订单错误: {str(e)}")

@router.put("/orders/{order_id}")
def update_order(order_id: int, order: OrderCreate):
    conn = get_db()
    try:
        max_retries = 3
        for attempt in range(max_retries):
            try:
                # 检查订单是否存在
                before_result = conn.selectData("SELECT COUNT(*) as count FROM orders WHERE id = ?", [order_id])
                before_count = before_result[0]['count'] if before_result else 0

                if before_count == 0:
                    print(f"订单 ID {order_id} 不存在")
                    raise HTTPException(status_code=404, detail=f"订单 ID {order_id} 不存在")

                # 执行更新
                query = QSqlQuery()
                query.prepare(
                    "UPDATE orders SET customer_name = ?, customer_email = ?, order_date = ?, "
                    "product_id = ?, quantity = ?, price = ? WHERE id = ?"
                )
                query.addBindValue(order.customer_name)
                query.addBindValue(order.customer_email)
                query.addBindValue(order.order_date)
                query.addBindValue(order.product_id)
                query.addBindValue(order.quantity)
                query.addBindValue(order.price)
                query.addBindValue(order_id)

                if not query.exec_():
                    error_text = query.lastError().text()
                    print(f"SQL执行失败：{error_text}")
                    raise HTTPException(status_code=500, detail=f"更新订单失败: {error_text}")

                return {"message": f"订单 {order_id} 更新成功"}
            except Exception as e:
                print(f"第 {attempt + 1} 次尝试更新订单失败: {e}")
                if attempt < max_retries - 1:
                    time.sleep(0.5)
                else:
                    raise HTTPException(status_code=500, detail="更新订单失败")
    except Exception as e:
        print(f"更新订单错误: {e}")
        raise HTTPException(status_code=500, detail=f"更新订单错误: {str(e)}")

@router.delete("/orders/{order_id}")
def delete_order(order_id: int):
    conn = get_db()
    try:
        print(f"尝试删除订单 ID: {order_id}")

        # 增加重试机制
        max_retries = 3
        for attempt in range(max_retries):
            try:
                # 先检查订单是否存在
                before_result = conn.selectData("SELECT COUNT(*) as count FROM orders WHERE id = ?", [order_id])
                before_count = before_result[0]['count'] if before_result else 0

                if before_count == 0:
                    print(f"订单 ID {order_id} 不存在")
                    raise HTTPException(status_code=404, detail=f"订单 ID {order_id} 不存在")

                # 执行删除
                query = QSqlQuery()
                query.prepare("DELETE FROM orders WHERE id = ?")
                query.addBindValue(order_id)

                success = query.exec_()
                if not success:
                    error_text = query.lastError().text()
                    print(f"SQL执行失败：{error_text}")
                    raise HTTPException(status_code=500, detail=f"删除订单失败: {error_text}")

                # 检查影响的行数
                rows_affected = query.numRowsAffected()
                print(f"删除操作影响了 {rows_affected} 行")

                print(f"订单 {order_id} 删除操作执行完成")
                return {"message": f"订单 {order_id} 删除成功"}

            except Exception as e:
                print(f"第 {attempt + 1} 次尝试删除订单失败: {e}")
                if attempt < max_retries - 1:
                    time.sleep(0.5)  # 等待0.5秒后重试
                else:
                    raise HTTPException(status_code=500, detail="删除订单失败")

    except HTTPException as e:
        print(f"HTTP 异常: {e.detail}")
        raise e
    except Exception as e:
        print(f"数据库错误: {e}")
        raise HTTPException(status_code=500, detail=f"数据库错误: {str(e)}")